//
//  TreeSqlManager.swift
//  tree_ai
//
//  Created by yumini on 2024/5/27.
//

import Foundation
import SQLite


struct Tree {
    var id: Int64
    var name: String
    var category: String //识别类型
    var image_name: String
    var image_url: String
    var image_id: String
    var image_file_size: Int64
    var desc: String
    var create_time: Int64
    var update_time: Int64
}


class TreeSqlManager {
    
    static let shared = TreeSqlManager()
    public let snowFlake = SnowflakeSwift(IDCID: 4, machineID: 30)
    private let db: Connection?
    
    private let trees = Table("trees")
    let id = Expression<Int64>("id")
     let name = Expression<String>("name")
    let category = Expression<String>("category")
     let image_name = Expression<String>("image_name")
     let image_url = Expression<String>("image_url")
     let image_id = Expression<String>("image_id")
     let image_file_size = Expression<Int64>("image_file_size")
    let desc = Expression<String>("desc")
     let create_time = Expression<Int64>("create_time")
     let update_time = Expression<Int64>("update_time")
    
    public var testIpv6Host = ""
    
    private init() {
        do {
            let documentPaths = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory,
                                                                    FileManager.SearchPathDomainMask.userDomainMask, true)
            let documnetPath = documentPaths[0]
            //documnetPath = "/Users/yumini/Desktop/appdb.sqlite3"
            var dbPath = documnetPath + "/treedb.sqlite3"
            // dbPath = "/Users/yumini/Desktop/appdb.sqlite3"
            db = try Connection(dbPath)
            createTable()
        } catch {
            db = nil
            print("Unable to open database:")
        }
        
    }
    
    func createTable() {
        do {
            try db?.run(trees.create { t in
                t.column(id, primaryKey: true)
                t.column(name)
                t.column(category)
                t.column(desc)
                t.column(image_name)
                t.column(image_url)
                t.column(image_id)
                t.column(image_file_size)
                t.column(create_time)
                t.column(update_time)
            })
        } catch {
            print("Unable to create table")
        }
    }
    
    func insertTree(category: String,image_name: String, image_url: String, image_id: String, image_file_size: Int64) -> Int64{
        var rowId = Int64(-1)
        do {
            let createTime = Int64(Date().timeIntervalSince1970)
            let updateTime = Int64(Date().timeIntervalSince1970)
            let insert = trees.insert(self.name <- "", self.desc <- "", self.category <- category,self.image_name <- image_name, self.image_url <- image_url, self.image_id <- image_id, self.image_file_size <- image_file_size, self.create_time <- createTime, self.update_time <- updateTime)
            rowId = (try db?.run(insert))!
        } catch {
            print("Unable to insert tree")
        }
        return rowId
    }
    
    func updateTree(id: Int64, name: String , desc: String) {
        do {
            let updateTime = Int64(Date().timeIntervalSince1970)
            let oneTree = trees.filter(self.id == id)
            let update = oneTree.update(self.name <- name, self.desc <- desc, self.update_time <- updateTime)
            try db?.run(update)
        } catch {
            print("Unable to update tree")
        }
    }
    
    func getOneTree(id: Int64) -> Tree?{
        
        var trees = [Tree]()
        do {
            let select = self.trees.filter(self.id == id)
            for tree in try db!.prepare(select) {
                let oneTree = Tree(id: tree[self.id], name: tree[self.name], category: tree[self.category],image_name: tree[self.image_name], image_url: tree[self.image_url], image_id: tree[self.image_id], image_file_size: tree[self.image_file_size], desc: tree[self.desc], create_time: tree[self.create_time], update_time: tree[self.update_time])
                trees.append(oneTree)
            }
        } catch {
            print("Unable to get tree")
        }
        if trees.count > 0 {
            return trees[0]
        }else {
            return nil
        }
        
    }
    
    func getAllTrees() -> [Tree] {
        var trees = [Tree]()
        do {
            for tree in try db!.prepare(self.trees) {
                trees.append(Tree(id: tree[self.id],
                                  name: tree[self.name],
                                  category: tree[self.category], 
                                  image_name: tree[self.image_name],
                                  image_url: tree[self.image_url],
                                  image_id: tree[self.image_id],
                                  image_file_size: tree[self.image_file_size],
                                  desc: tree[self.desc],
                                  create_time: tree[self.create_time],
                                  update_time: tree[self.update_time]
                ))
            }
        } catch {
            print("Unable to get trees")
        }
        return trees
    }
    
}
